﻿using DigDes.DSchool.SUPS.DataAccess.Interface;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DigDes.DSchool.SUPS.DataAccess.Database
{
    public class FileLoaderDAC
    {        
        private static readonly string ConnectionStringKey = "SUPSDB";

        public static void AddOneRow(Dislocation70LoaderDTO row)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {

                var sqlCmd = new SqlCommand("SetDislocationRow", sqlConn);

                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.AddWithValue("CarNumber", row.CarNumber);
                sqlCmd.Parameters.AddWithValue("Weight", row.Weight);
                sqlCmd.Parameters.AddWithValue("ArrivalStation", row.ArrivalStation);
                sqlCmd.Parameters.AddWithValue("CargoCode", row.CargoCode);
                sqlCmd.Parameters.AddWithValue("ConsigneeCode", row.ConsigneeCode);
                sqlCmd.Parameters.AddWithValue("CarParkType", row.CarParkType);
                sqlCmd.Parameters.AddWithValue("DispatchStation", row.DispatchStation);
                sqlCmd.Parameters.AddWithValue("OperationCode", row.OperationCode);
                sqlCmd.Parameters.AddWithValue("OperationDateTime", row.OperationDateTime);

                sqlCmd.Parameters.AddWithValue("OperationStation", row.OperationStation);
                sqlCmd.Parameters.AddWithValue("RoadOfDelivery", row.RoadOfDelivery);
                sqlCmd.Parameters.AddWithValue("RoadOfReception", row.RoadOfReception);
                sqlCmd.Parameters.AddWithValue("TrainIndex", row.TrainIndex);
                sqlCmd.Parameters.AddWithValue("TrainNumber", row.TrainNumber);



                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();

            }
        }

        public static void AddAllRows(IEnumerable<Dislocation70LoaderDTO> rows)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            using (var sqlConn = new SqlConnection(connectionString))
            {
                foreach (var row in rows)
                {
                    using (var sqlCmd = sqlConn.CreateCommand())
                    {
                        sqlCmd.CommandText = "insert into Dislocation (CarNumber, Weight, ArrivalStation, CargoCode, ConsigneeCode, CarParkType, DispatchStation, OperationCode, OperationDateTime, OperationStation, RoadOfDelivery, RoadOfReception, TrainIndex,TrainNumber)";
                        sqlCmd.CommandText += "values (@CarNumber, @Weight, @ArrivalStation, @CargoCode, @ConsigneeCode, @CarParkType, @DispatchStation, @OperationCode, @OperationDateTime, @OperationStation, @RoadOfDelivery, @RoadOfReception, @TrainIndex,@TrainNumber)";

                        sqlCmd.Parameters.AddWithValue("@CarNumber", row.CarNumber);
                        sqlCmd.Parameters.AddWithValue("@Weight", row.Weight);
                        sqlCmd.Parameters.AddWithValue("@ArrivalStation", row.ArrivalStation);
                        sqlCmd.Parameters.AddWithValue("@CargoCode", row.CargoCode);
                        sqlCmd.Parameters.AddWithValue("@ConsigneeCode", row.ConsigneeCode);
                        sqlCmd.Parameters.AddWithValue("@CarParkType", row.CarParkType);
                        sqlCmd.Parameters.AddWithValue("@DispatchStation", row.DispatchStation);
                        sqlCmd.Parameters.AddWithValue("@OperationCode", row.OperationCode);
                        sqlCmd.Parameters.AddWithValue("@OperationDateTime", (System.Data.SqlTypes.SqlDateTime)row.OperationDateTime);
                        sqlCmd.Parameters.AddWithValue("@OperationStation", row.OperationStation);
                        sqlCmd.Parameters.AddWithValue("@RoadOfDelivery", row.RoadOfDelivery);
                        sqlCmd.Parameters.AddWithValue("@RoadOfReception", row.RoadOfReception);
                        sqlCmd.Parameters.AddWithValue("@TrainIndex", row.TrainIndex);
                        sqlCmd.Parameters.AddWithValue("@TrainNumber", row.TrainNumber);

                        sqlConn.Open();
                        sqlCmd.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}
